﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using Microsoft.Win32;

namespace DataAccess
{
    public class DataAccess
    {
        private SqlConnection conn;
        static bool hasTransaction = false;
        static SqlTransaction objTransaction = null; 

        public DataAccess()
        {
            
        }

        public String ConnectionString
        {
            //get { return ConfigurationManager.ConnectionStrings["SHTT.Properties.Settings.connectionString"].ConnectionString; }            
            get { return "Data Source=" + RegGetValue("ServerName") + ";Initial Catalog=" + RegGetValue("DatabaseName") + ";Persist Security Info=True;User ID=" + RegGetValue("UserName") + ";Password=" + RegGetValue("Password"); }
        }

        public String oleDbConnectionString
        {
            //get { return ConfigurationManager.ConnectionStrings["SHTT.Properties.Settings.connectionString"].ConnectionString; }            
            get { return "Provider=SQLNCLI.1;Data Source=" + RegGetValue("ServerName") + ";Initial Catalog=" + RegGetValue("DatabaseName") + ";User ID=" + RegGetValue("UserName") + ";Password=" + RegGetValue("Password"); }
        }

        private string RegGetValue(string subKeyName)
        {
            const string userRoot = "HKEY_LOCAL_MACHINE\\SOFTWARE";
            const string subkey = "BEGIN";//SHTT
            const string keyName = userRoot + "\\" + subkey;

            string subKeyValue = (string)Registry.GetValue(keyName, subKeyName, "");
            return subKeyValue;
        }

        public void RegSetValue(string subKeyName, string subKeyValue)
        {
            const string userRoot = "HKEY_LOCAL_MACHINE\\SOFTWARE";
            const string subkey = "BEGIN";
            const string keyName = userRoot + "\\" + subkey;

            Registry.SetValue(keyName, "", RegistryValueKind.String);
            Registry.SetValue(keyName, subKeyName, subKeyValue);
        }

        // Insert, Delete, Update dùng store procedure, mảng biến truyền vào
        public void ExecuteNonQuery(string storedProcedureName, params SqlParameter[] arrParam)
        {
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        public int ExecuteNonQueryText(string _query, params SqlParameter[] arrParam)
        {
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(_query, conn);
            cmd.CommandType = CommandType.Text;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                int rowsAffected = cmd.ExecuteNonQuery();
                return rowsAffected;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }


        public DataTable ExecuteSelectQuery(String _query, SqlParameter[] arrParam)
        {
            DataTable dt = new DataTable();
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(_query, conn);
            cmd.CommandType = CommandType.Text;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Return datatable
        public DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataTable dt = new DataTable();
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Return dataset
        public DataSet ExecuteDataSet(string storedProcedureName, params SqlParameter[] arrParam)
        {
            DataSet Ds = new DataSet();
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(Ds);
                return Ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }
        //return dataset for sql

        public DataSet ExecuteDataSetQuery(string sql,string srcTable)
        {
            DataSet Ds = new DataSet();

            conn = new SqlConnection(ConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                da.Fill(Ds, srcTable);
                return Ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        public DataTable ReadExcel(string fileName)
        {

            string drPathFull = fileName;
            string strConn;
            DataSet dsExcel = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + drPathFull + ";" +
            "Extended Properties=Excel 8.0;";

            SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM {0}$", strConn);
            myCommand.Fill(dsExcel, "ExcelInfo");
            return dsExcel.Tables["ExcelInfo"];

        }

        // Thực thi insert, update, delete dùng câu lệnh query
        public void ExecuteNonQueryString(string sql)
        {
            conn = new SqlConnection(ConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.Text;
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally 
            { 
                conn.Close(); 
            }
        }

        // Dùng câu lệnh query đổ dữ liệu ra bảng
        public DataTable ExecuteSelectQueryString(string sql)
        {
            conn = new SqlConnection(ConnectionString);
            conn.Open();

            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            try
            {
                da.Fill(dt);
            }
            finally 
            { 
                conn.Close(); 
            }
            return dt;
        }

        // Insert, delete, update (return SCOPE_IDENTITY)
        public int ExecuteNonQueryScopeIdentity(int returnParemter, string storedProcedureName, params SqlParameter[] arrParam)
        {
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                cmd.ExecuteNonQuery();
                return int.Parse(cmd.Parameters[returnParemter].Value.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Get ID with ExecuteNonQuery
        public int GetInsertedIdWithExecuteNonQuery(String storedProcedureName, params SqlParameter[] arrParam)
        {
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            try
            {
                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                        cmd.Parameters.Add(param);
                }

                cmd.ExecuteNonQuery();

                int insertedId = (int)cmd.Parameters[cmd.Parameters.Count - 1].Value;
                return insertedId;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        // Return a value
        public int ExecuteScalar(string storedProcedureName, params SqlParameter[] arrParam)
        {
            conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(storedProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {

                if (arrParam != null)
                {
                    foreach (SqlParameter param in arrParam)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                int rowsAffected = Convert.ToInt32(cmd.ExecuteScalar());
                return rowsAffected;
            }
            catch (Exception ex)
            {
                throw new Exception("Error: " + ex.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }
    }
}
